/*
 by txdnet.cn tonydon 2012.8.16
 */
package txdnet.db;

import java.io.Reader;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import txdnet.util.FieldUtil;
import txdnet.util.Logger;
import txdnet.util.StaticMark;

/**
 * @author txdnet
 */
public class DbUtil {

    private DbUtil() {
    }

    public static Connection getConnection() {
        return PoolFactory.getConnection();
    }

    public static Connection getConnection(boolean isAutoCommit) {
        Connection c = PoolFactory.getConnection();
        try {
            c.setAutoCommit(isAutoCommit);
        } catch (Exception e) {
            Logger.error(DbUtil.class, e.toString());
            c = null;
        }
        return c;
    }

    /*
     * 通过SQL COUNT() 简单得到记录总数.
     */
    public static int getRecordCount(String sqlCount) {
        Connection conn = null;
        ResultSet rs = null;
        Statement st = null;
        int count = 0;
        try {
            conn = PoolFactory.getConnection();
            st = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            st.setFetchSize(1);
            st.setMaxRows(1);
            rs = st.executeQuery(sqlCount);
            rs.first();
            count = rs.getInt(1);
        } catch (Exception e) {
            Logger.error(DbUtil.class, e.toString());
            count = -1;
        } finally {
            DbUtil.close(rs, st, conn);
        }
        return count;
    }

    /*
     *@note 将CLOB转为String
     */
    public static String parseClobToStr(Object clob, int charbuff) throws Exception {
        if (clob == null) {
            return StaticMark.STR_EMPTY;
        }
        StringBuilder sb = new StringBuilder();
        if (clob instanceof Clob) {
            char c[] = new char[charbuff];
            Reader reader = ((Clob) clob).getCharacterStream();
            while (reader.read(c, 0, charbuff) != -1) {
                sb.append(c);
            }
        } else {
            sb.append(clob.toString());
        }
        return sb.toString().trim();
    }

    /*
     *  简单的更新操作
     */
    public static int doUpdateSql(String sql) {
        Connection conn = null;
        Statement st = null;
        int r = 0;
        try {
            conn = PoolFactory.getConnection();
            st = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            r = st.executeUpdate(sql);
        } catch (Exception e) {
            Logger.error(DbUtil.class, e.toString());
            r = -1;
        } finally {
            DbUtil.close(st, conn);
        }
        return r;
    }

    public static PreparedStatement getPstmt(String sql) throws SQLException {
        return PoolFactory.getConnection().prepareStatement(sql);
    }

    public static PreparedStatement getPstmt(boolean isAutoCommit, String sql) throws SQLException {
        return DbUtil.getConnection(isAutoCommit).prepareStatement(sql);
    }


    /*
     * 将ResultSet结果集包装到Map对应值
     * return map;
     */
    public static Map<String, Object> parseRsToMap(ResultSet rs) throws Exception {
        ResultSetMetaData rsmd = rs.getMetaData();
        int colnum = rsmd.getColumnCount();
        if (rs.next()) {
           Map<String, Object> m = new HashMap<String, Object>(colnum);
            for (int i = 1; i <= colnum; i++) {
                m.put(rsmd.getColumnName(i).replace(StaticMark.STR_UNDER_LINE, 
                        StaticMark.STR_EMPTY), 
                        rs.getObject(i));
            }
            return m;
        }
        return Collections.emptyMap();
    }

    /*
     *  将ResultSet 包装到 含特定 Class POJO中
     */
    public static Object parseRsToObject(ResultSet rs, Class clazz) throws Exception {
        ResultSetMetaData rsmd = rs.getMetaData();
        int colnum = rsmd.getColumnCount();
        Field[] fs = clazz.getDeclaredFields();
        Object obj = null;
        if (rs.next()) {
            obj = clazz.newInstance();
            Field f;
            Object v;
            for (int i = 1; i <= colnum; i++) {
                v = rs.getObject(i);
                for (int j = 0; j < fs.length; j++) {
                    f = fs[j];
                    if (f.getName().equalsIgnoreCase(rsmd.getColumnName(i)
                            .replace(StaticMark.STR_UNDER_LINE,
                            StaticMark.STR_EMPTY))) {
                        FieldUtil.setSingleProperty(f, obj, v);
                        break;
                    }
                }
            }
        }
        return obj;
    }

    /*
     *  将ResultSet 结果 包装到 含Map的List中
     */
    public static List<Map<String,Object>> parseRsToList(ResultSet rs) throws Exception {
        ResultSetMetaData rsmd = rs.getMetaData();
        int colnum = rsmd.getColumnCount();
        Map<String, Object> m;
        List<Map<String,Object>> ls = new ArrayList<Map<String,Object>>(21);
        while (rs.next()) {
            m = new HashMap<String, Object>(colnum);
            for (int i = 1; i <= colnum; i++) {
                m.put(rsmd.getColumnName(i).replace(StaticMark.STR_UNDER_LINE, StaticMark.STR_EMPTY), 
                        rs.getObject(i));
            }
            ls.add(m);
        }
        return ls;
    }

    public static List<Map<String,Object>> parseRsToMapList(ResultSet rs) throws Exception {
        return DbUtil.parseRsToList(rs);
    }

    /*
     *  将ResultSet 包装到 Class POJO 的List 中
     */
    public static List<Object> parseRsToObjectList(ResultSet rs, Class clazz) throws Exception {
        ResultSetMetaData rsmd = rs.getMetaData();
        int colnum = rsmd.getColumnCount();
        Field[] fs = clazz.getDeclaredFields();
        List<Object> ls = new ArrayList<Object>(21);
        Object obj;
        while (rs.next()) {
            Field f;
            Object v;
            obj = clazz.newInstance();
            for (int i = 1; i <= colnum; i++) {
                v = rs.getObject(i);
                for (int j = 0; j < fs.length; j++) {
                    f = fs[j];
                    if (f.getName().equalsIgnoreCase(rsmd.getColumnName(i)
                            .replace(StaticMark.STR_UNDER_LINE, StaticMark.STR_EMPTY))) {
                        FieldUtil.setSingleProperty(f, obj, v);
                        break;
                    }
                }
            }
            ls.add(obj);
        }
        return ls;
    }

    /*
     *  将参数更具索引位置对预处理语句进行设置
     */
    public static void setPreparedStatementValue(PreparedStatement ps, Object[] args) throws SQLException {
        for (int i = 0; i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
    }

    private static void setPreparedStatementValue(PreparedStatement ps, int pIndex, Object value, int sqlType) throws Exception {
        if (sqlType == Types.VARCHAR || sqlType == Types.LONGVARCHAR || sqlType == Types.CLOB) {
            ps.setString(pIndex, value.toString());
        } else if (sqlType == Types.INTEGER) {
            ps.setInt(pIndex, (Integer) value);
        } else if (sqlType == Types.BIGINT) {
            ps.setLong(pIndex, (Long) value);
        } else if (sqlType == Types.DOUBLE) {
            ps.setDouble(pIndex, (Double) value);
        } else if (sqlType == Types.DATE) {//DATE
            if (value instanceof String) {
                ps.setDate(pIndex, Date.valueOf(value.toString()));
            } else {
                ps.setDate(pIndex, (Date) value);
            }
        } else if (sqlType == Types.TIMESTAMP) {//TIMESTAMP
            if (value instanceof String) {
                ps.setTimestamp(pIndex, Timestamp.valueOf(value.toString()));
            } else {
                ps.setTimestamp(pIndex, (Timestamp) value);
            }
        } else {
            ps.setObject(pIndex, value);
        }
    }

    public static void setPreparedStatementValue(PreparedStatement ps, Object[] args, int[] sqlTypes) throws Exception {
        if (args == null || sqlTypes == null || (args.length != sqlTypes.length)) {
            throw new java.lang.IllegalArgumentException("parameter wrong, the length of args should equal with the length of sqlTypes");
        }
        for (int i = 0; i < args.length; i++) {
            DbUtil.setPreparedStatementValue(ps, i + 1, args[i], sqlTypes[i]);
        }
    }

    /*
     * 关闭数据库连接资源
     * close resultset, statement,connection
     */
    public static void close(ResultSet rs, Statement st, Connection c) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                Logger.error(DbUtil.class, e.toString());
            }
        }

        if (st != null) {
            try {
                if (!st.isClosed()) {
                    st.close();
                }
            } catch (Exception e) {
                Logger.error(DbUtil.class, e.toString());
            }
        }
        if (c != null) {
            try {
                if (!c.isClosed()) {
                    c.close();
                }
            } catch (Exception e) {
                Logger.error(DbUtil.class, e.toString());
            }
        }
    }

    public static void close(ResultSet rs, Statement st) {

        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                Logger.error(DbUtil.class, e.toString());
            }
        }

        if (st != null) {
            try {
                Connection c = st.getConnection();
                if (!st.isClosed()) {
                    st.close();
                }
                if (c != null && !c.isClosed()) {
                    c.close();
                }
            } catch (Exception e) {
                Logger.error(DbUtil.class, e.toString());
            }
        }
    }

    public static void close(Statement st, Connection c) {
        if (st != null) {
            try {
                if (!st.isClosed()) {
                    st.close();
                }
            } catch (Exception e) {
                Logger.error(DbUtil.class, e.toString());
            }
        }
        if (c != null) {
            try {
                if (!c.isClosed()) {
                    c.close();
                }
            } catch (Exception e) {
                Logger.error(DbUtil.class, e.toString());
            }
        }
    }

    public static void close(Statement st) {
        if (st != null) {
            try {
                Connection c = st.getConnection();
                if (!st.isClosed()) {
                    st.close();
                }
                if (c != null && !c.isClosed()) {
                    c.close();
                }
            } catch (Exception e) {
                Logger.error(DbUtil.class, e.toString());
            }
        }

    }

    public static void close(Connection c) {
        if (c != null) {
            try {
                if (!c.isClosed()) {
                    c.close();
                }
            } catch (Exception e) {
                Logger.error(DbUtil.class, e.toString());
            }
        }
    }

    public static void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                Logger.error(DbUtil.class, e.toString());
            }
        }
    }
}